package com.studyyoun.android.nonghe.home.sql;
/**
 * Created by zhaolong on 2017/9/29.
 * 站在顶峰，看世界
 * 落在谷底，思人生
 */

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Base64;

import com.studyyoun.android.nonghe.home.model.ArtClassModel;
import com.studyyoun.android.nonghe.home.model.ArtSpecliesModel;
import com.studyyoun.android.nonghe.home.model.ArtModel;
import com.studyyoun.android.nonghe.sql.CommonSqlBaseDao;
import com.studyyoun.android.nonghe.sql.SqlDBHelper;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.Semaphore;

/**
 * class infation
 */
public class ArtSqlDBHelper extends CommonSqlBaseDao {

    private SQLiteDatabase mLiteDatabase;

    /**
     * CountDownLatch是JAVA提供在java.util.concurrent包下的一个辅助类，
     * 可以把它看成是一个计数器，其内部维护着一个count计数，只不过对这个计数器的操作都是原子操作，同时只能有一个线程去操作这个计数器，
     * CountDownLatch通过构造函数传入一个初始计数值，调用者可以通过调用CounDownLatch对象的cutDown()方法，来使计数减1；
     * 如果调用对象上的await()方法，那么调用者就会一直阻塞在这里，直到别人通过cutDown方法，将计数减到0，才可以继续执行
     */
    /**
     * 在这里创建了一个计数器，初始值为0 ，也就是说当前有0个线程在操作
     */
    private static CountDownLatch latch =
            new CountDownLatch(0);
    /**
     * 创建一个信号量，初始值为1 只允许一个线程来操作
     */
    private static Semaphore lock =
            new Semaphore(1, true);


    public ArtSqlDBHelper(Context context) {
        super(context);
        latch.countDown();
    }

    public void insertArtList(List<ArtModel> artModelList) {

        try {
            if (latch.getCount() == 0 || mLiteDatabase == null) {
                mLiteDatabase = mSqlDBHelper.getWritableDatabase();
            }
            latch.await();
            lock.acquire();


            // 开始
            mLiteDatabase.beginTransaction();

            // 循环一万次
            for (ArtModel artModel : artModelList) {
                insertOrUpdateArtModelFunction(artModel);
            }


            // 成功
            mLiteDatabase.setTransactionSuccessful();

        } catch (InterruptedException e) {
            e.printStackTrace();
        } finally {
            mLiteDatabase.endTransaction();
            lock.release();

            if (latch.getCount() == 1) {
                mLiteDatabase.close();
                mLiteDatabase = null;
            }

            latch.countDown();
        }


    }

    private void insertOrUpdateArtModelFunction(ArtModel artModel) {
        boolean isInsertNew;
        String querySql = "select * from " + SqlDBHelper.TABLE_NAME_ART + " where art_id=" + artModel.id;


        Cursor cursor = mLiteDatabase.rawQuery(querySql, null);

        if (cursor == null || cursor.getCount() == 0) {
            isInsertNew = true;
        } else {
            isInsertNew = false;
        }


        if (cursor != null) {
            cursor.close();
        }

        if (isInsertNew) {


            String sql = "insert into ";

            StringBuilder builder = new StringBuilder(sql);
            builder.append(SqlDBHelper.TABLE_NAME_ART);
            builder.append(" (art_id,art_name,art_flag,art_img,art_content,art_creat_time,art_parise_count,art_read_count,art_is_top,art_is_recommend) ");
            builder.append(" values( ");

            builder.append(artModel.id);
            builder.append(",");

            builder.append("'");
            builder.append(artModel.artName);
            builder.append("'");
            builder.append(",");

            builder.append("'");
            builder.append(Base64.encodeToString(artModel.artFalg.getBytes(), Base64.DEFAULT));
            builder.append("'");
            builder.append(",");

            builder.append("'");
            builder.append(Base64.encodeToString(artModel.artImageUrl.getBytes(), Base64.DEFAULT));
            builder.append("'");
            builder.append(",");

            builder.append("'");
            builder.append(Base64.encodeToString(artModel.artContent.getBytes(), Base64.DEFAULT));
            builder.append("'");
            builder.append(",");

            builder.append("'");
            builder.append(artModel.artCreateTime);
            builder.append("'");
            builder.append(",");

            builder.append(artModel.artParise);
            builder.append(",");

            builder.append(artModel.artReadCount);
            builder.append(",");
            builder.append(artModel.artIsTop);
            builder.append(",");
            builder.append(artModel.artIsRecommend);


            builder.append(" )");

            mLiteDatabase.execSQL(builder.toString());
            /**
             * 对应的专栏信息
             */
            List<ArtSpecliesModel> specilModels = artModel.specilModels;
            if (specilModels != null) {
                for (ArtSpecliesModel specilModel : specilModels) {

                    String queryItemClassSql = "select * from " + SqlDBHelper.TABLE_NAME_ART_CLASS + " where art_id=" + artModel.getId() + " and art_clas_id= " + specilModel.getId();

                    Cursor cursor1 = mLiteDatabase.rawQuery(queryItemClassSql, null);
                    boolean clasIsNuw = true;

                    if (cursor1 != null && cursor1.getCount() > 0) {
                        clasIsNuw = false;
                        cursor1.close();
                    }


                    ContentValues classValues = new ContentValues();
                    classValues.put("art_id", artModel.getId());
                    classValues.put("art_clas_id", specilModel.getId());
                    classValues.put("art_clas_name", specilModel.getArtSpecilName());
                    classValues.put("art_clas_content", specilModel.getContent());
                    classValues.put("art_clas_creat_time", specilModel.getCreateTime());
                    classValues.put("art_clas_creat_image", specilModel.getImageUrl());
                    classValues.put("art_clas_creat_uid", specilModel.getUid());

                    if (clasIsNuw) {
                        mLiteDatabase.insert(SqlDBHelper.TABLE_NAME_ART_CLASS, null, classValues);
                    } else {
                        mLiteDatabase.update(SqlDBHelper.TABLE_NAME_ART_CLASS, classValues, "art_id=? and art_clas_id=?", new String[]{String.valueOf(artModel.getId()), String.valueOf(specilModel.getId())});
                    }

                }
            }

            /**
             * 分类相关
             */
            List<ArtClassModel> articleClassModels = artModel.articleClassModels;
            if (articleClassModels != null) {
                for (ArtClassModel articleClassModel : articleClassModels) {
                    String queryItemClassSql = "select * from " + SqlDBHelper.TABLE_NAME_ART_SPE + " where art_id=" + artModel.getId() + " and art_spe_id= " + articleClassModel.getId();

                    Cursor cursor1 = mLiteDatabase.rawQuery(queryItemClassSql, null);
                    boolean clasIsNuw = true;

                    if (cursor1 != null && cursor1.getCount() > 0) {
                        clasIsNuw = false;
                        cursor1.close();
                    }

                    ContentValues classValues = new ContentValues();
                    classValues.put("art_id", artModel.getId());
                    classValues.put("art_spe_id", articleClassModel.getId());
                    classValues.put("art_spe_name", articleClassModel.getArtClassName());

                    if (clasIsNuw) {
                        mLiteDatabase.insert(SqlDBHelper.TABLE_NAME_ART_SPE, null, classValues);
                    } else {
                        mLiteDatabase.update(SqlDBHelper.TABLE_NAME_ART_SPE, classValues, "art_id=? and art_spe_id=?", new String[]{String.valueOf(artModel.getId()), String.valueOf(articleClassModel.getId())});
                    }
                }
            }

            /**
             * 点赞相关
             */
            List<String> articlPariseModels = artModel.articlPariseModels;
            if (articlPariseModels != null && articlPariseModels.size() > 0) {
                for (String articlPariseModel : articlPariseModels) {


                    String queryItemClassSql = "select * from " + SqlDBHelper.TABLE_NAME_RE_ART_PARISE + " where art_id=" + artModel.getId() + " and user_id=" + articlPariseModel;

                    Cursor cursor1 = mLiteDatabase.rawQuery(queryItemClassSql, null);
                    boolean clasIsNuw = true;

                    if (cursor1 != null && cursor1.getCount() > 0) {
                        clasIsNuw = false;
                        cursor1.close();
                    }


                    ContentValues classValues = new ContentValues();
                    classValues.put("art_id", artModel.getId());
                    classValues.put("user_id", articlPariseModel);

                    if (clasIsNuw) {
                        mLiteDatabase.insert(SqlDBHelper.TABLE_NAME_RE_ART_PARISE, null, classValues);
                    } else {
                        mLiteDatabase.update(SqlDBHelper.TABLE_NAME_RE_ART_PARISE, classValues, "art_id=? and user_id=?", new String[]{String.valueOf(artModel.getId()), articlPariseModel});
                    }


                }
            }

            /**
             * 收藏相关
             */
            List<String> articlCollectModels = artModel.articlCollectModels;
            if (articlCollectModels != null && articlCollectModels.size() > 0) {
                for (String articlCollectId : articlCollectModels) {


                    String queryItemClassSql = "select * from " + SqlDBHelper.TABLE_NAME_RE_ART_Collect + " where art_id=" + artModel.getId() + " and user_id=" + articlCollectId;

                    Cursor cursor1 = mLiteDatabase.rawQuery(queryItemClassSql, null);
                    boolean clasIsNuw = true;

                    if (cursor1 != null && cursor1.getCount() > 0) {
                        clasIsNuw = false;
                        cursor1.close();
                    }


                    ContentValues classValues = new ContentValues();
                    classValues.put("art_id", artModel.getId());
                    classValues.put("user_id", articlCollectId);

                    if (clasIsNuw) {
                        mLiteDatabase.insert(SqlDBHelper.TABLE_NAME_RE_ART_Collect, null, classValues);
                    } else {
                        mLiteDatabase.update(SqlDBHelper.TABLE_NAME_RE_ART_Collect, classValues, "art_id=? and user_id=?", new String[]{String.valueOf(artModel.getId()), articlCollectId});
                    }


                }
            }


        } else {

            ContentValues value = new ContentValues();

            value.put("art_name", artModel.artName);
            value.put("art_flag", Base64.encodeToString(artModel.artFalg.getBytes(), Base64.DEFAULT));

            value.put("art_content", Base64.encodeToString(artModel.artContent.getBytes(), Base64.DEFAULT));
            value.put("art_img", Base64.encodeToString(artModel.artImageUrl.getBytes(), Base64.DEFAULT));
            value.put("art_creat_time", artModel.artCreateTime);

            value.put("art_parise_count", artModel.artParise);
            value.put("art_read_count", artModel.artReadCount);
            value.put("art_is_top", artModel.artIsTop);
            value.put("art_is_recommend", artModel.artIsRecommend);
            String[] args = {String.valueOf(artModel.id)};
            mLiteDatabase.update(SqlDBHelper.TABLE_NAME_ART, value, "art_id=?", args);

            List<ArtSpecliesModel> specilModels = artModel.specilModels;
            if (specilModels != null) {
                for (ArtSpecliesModel specilModel : specilModels) {

                    String queryItemClassSql = "select * from " + SqlDBHelper.TABLE_NAME_ART_CLASS + " where art_id=" + artModel.getId() + " and art_clas_id= " + specilModel.getId();

                    Cursor cursor1 = mLiteDatabase.rawQuery(queryItemClassSql, null);
                    boolean clasIsNuw = true;

                    if (cursor1 != null && cursor1.getCount() > 0) {
                        clasIsNuw = false;
                        cursor1.close();
                    }


                    ContentValues classValues = new ContentValues();
                    classValues.put("art_id", artModel.getId());
                    classValues.put("art_clas_id", specilModel.getId());
                    classValues.put("art_clas_name", specilModel.getArtSpecilName());
                    classValues.put("art_clas_content", specilModel.getContent());
                    classValues.put("art_clas_creat_time", specilModel.getCreateTime());
                    classValues.put("art_clas_creat_image", specilModel.getImageUrl());
                    classValues.put("art_clas_creat_uid", specilModel.getUid());

                    if (clasIsNuw) {
                        mLiteDatabase.insert(SqlDBHelper.TABLE_NAME_ART_CLASS, null, classValues);
                    } else {
                        mLiteDatabase.update(SqlDBHelper.TABLE_NAME_ART_CLASS, classValues, "art_id=? and art_clas_id=?", new String[]{String.valueOf(artModel.getId()), String.valueOf(specilModel.getId())});
                    }

                }
            }

            List<ArtClassModel> articleClassModels = artModel.articleClassModels;
            if (articleClassModels != null && articleClassModels.size() > 0) {
                for (ArtClassModel articleClassModel : articleClassModels) {
                    String queryItemClassSql = "select * from " + SqlDBHelper.TABLE_NAME_ART_SPE + " where art_id=" + artModel.getId() + " and art_spe_id= " + articleClassModel.getId();

                    Cursor cursor1 = mLiteDatabase.rawQuery(queryItemClassSql, null);
                    boolean clasIsNuw = true;

                    if (cursor1 != null && cursor1.getCount() > 0) {
                        clasIsNuw = false;
                        cursor1.close();
                    }

                    ContentValues classValues = new ContentValues();
                    classValues.put("art_id", artModel.getId());
                    classValues.put("art_spe_id", articleClassModel.getId());
                    classValues.put("art_spe_name", articleClassModel.getArtClassName());

                    if (clasIsNuw) {
                        mLiteDatabase.insert(SqlDBHelper.TABLE_NAME_ART_SPE, null, classValues);
                    } else {
                        mLiteDatabase.update(SqlDBHelper.TABLE_NAME_ART_SPE, classValues, "art_id=? and art_spe_id=?", new String[]{String.valueOf(artModel.getId()), String.valueOf(articleClassModel.getId())});
                    }
                }
            }

            List<String> articlPariseModels = artModel.articlPariseModels;
            if (articlPariseModels != null && articlPariseModels.size() > 0) {
                for (String articlPariseModel : articlPariseModels) {


                    String queryItemClassSql = "select * from " + SqlDBHelper.TABLE_NAME_RE_ART_PARISE + " where art_id=" + artModel.getId() + " and user_id= " + articlPariseModel;

                    Cursor cursor1 = mLiteDatabase.rawQuery(queryItemClassSql, null);
                    boolean clasIsNuw = true;

                    if (cursor1 != null && cursor1.getCount() > 0) {
                        clasIsNuw = false;
                        cursor1.close();
                    }


                    ContentValues classValues = new ContentValues();
                    classValues.put("art_id", artModel.getId());
                    classValues.put("user_id", articlPariseModel);

                    if (clasIsNuw) {
                        mLiteDatabase.insert(SqlDBHelper.TABLE_NAME_RE_ART_PARISE, null, classValues);
                    } else {
                        mLiteDatabase.update(SqlDBHelper.TABLE_NAME_RE_ART_PARISE, classValues, "art_id=? and user_id=?", new String[]{String.valueOf(artModel.getId()), articlPariseModel});
                    }


                }
            }
            /**
             * 收藏相关
             */
            List<String> articlCollectModels = artModel.articlCollectModels;
            if (articlCollectModels != null && articlCollectModels.size() > 0) {
                for (String articlCollectId : articlCollectModels) {


                    String queryItemClassSql = "select * from " + SqlDBHelper.TABLE_NAME_RE_ART_Collect + " where art_id=" + artModel.getId() + " and user_id=" + articlCollectId;

                    Cursor cursor1 = mLiteDatabase.rawQuery(queryItemClassSql, null);
                    boolean clasIsNuw = true;

                    if (cursor1 != null && cursor1.getCount() > 0) {
                        clasIsNuw = false;
                        cursor1.close();
                    }


                    ContentValues classValues = new ContentValues();
                    classValues.put("art_id", artModel.getId());
                    classValues.put("user_id", articlCollectId);

                    if (clasIsNuw) {
                        mLiteDatabase.insert(SqlDBHelper.TABLE_NAME_RE_ART_Collect, null, classValues);
                    } else {
                        mLiteDatabase.update(SqlDBHelper.TABLE_NAME_RE_ART_Collect, classValues, "art_id=? and user_id=?", new String[]{String.valueOf(artModel.getId()), articlCollectId});
                    }


                }
            }

        }
    }

    /**
     * 清除数据库中的数据
     */
    public void clearArtDb() {
        try {
            if (latch.getCount() == 0 || mLiteDatabase == null) {
                mLiteDatabase = mSqlDBHelper.getWritableDatabase();
            }
            latch.await();
            lock.acquire();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        String clearsQL = "delete from  " + SqlDBHelper.TABLE_NAME_ART;

        mLiteDatabase.execSQL(clearsQL);

        lock.release();

        if (latch.getCount() == 1) {
            mLiteDatabase.close();
            mLiteDatabase = null;
        }

        latch.countDown();
    }

    public void insertArt(ArtModel artModel) {
        try {
            if (latch.getCount() == 0 || mLiteDatabase == null) {
                mLiteDatabase = mSqlDBHelper.getWritableDatabase();
            }
            latch.await();
            lock.acquire();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

        insertOrUpdateArtModelFunction(artModel);

        lock.release();

        if (latch.getCount() == 1) {
            mLiteDatabase.close();
            mLiteDatabase = null;
        }

        latch.countDown();
    }


    public List<ArtModel> queryArtModelList(int type) {
        try {
            if (latch.getCount() == 0 || mLiteDatabase == null) {
                mLiteDatabase = mSqlDBHelper.getReadableDatabase();
            }
            latch.await();
            lock.acquire();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        List<ArtModel> list = new ArrayList<>();


        String sql = "select * from " + SqlDBHelper.TABLE_NAME_ART;
        if (type == 0) {
            sql = sql + " where art_is_top=1 or art_is_recommend=1";
        }

        Cursor cursor = mLiteDatabase.rawQuery(sql, null);
        if (cursor != null) {
            while (cursor.moveToNext()) {
                ArtModel artModel = new ArtModel();


                artModel.id = cursor.getInt(cursor.getColumnIndex("art_id"));
                artModel.artName = cursor.getString(cursor.getColumnIndex("art_name"));
                artModel.artFalg = new String(Base64.decode(cursor.getString(cursor.getColumnIndex("art_flag")).getBytes(), Base64.DEFAULT));
                artModel.artContent = new String(Base64.decode(cursor.getString(cursor.getColumnIndex("art_content")).getBytes(), Base64.DEFAULT));

                artModel.artCreateTime = cursor.getString(cursor.getColumnIndex("art_creat_time"));

                artModel.artParise = String.valueOf(cursor.getInt(cursor.getColumnIndex("art_parise_count")));
                artModel.artReadCount = String.valueOf(cursor.getInt(cursor.getColumnIndex("art_read_count")));

                artModel.artImageUrl = new String(Base64.decode(cursor.getString(cursor.getColumnIndex("art_img")).getBytes(), Base64.DEFAULT));

                artModel.artIsRecommend = cursor.getInt(cursor.getColumnIndex("art_is_recommend"));
                artModel.artIsTop = cursor.getInt(cursor.getColumnIndex("art_is_top"));
                List<ArtSpecliesModel> artClassModels = new ArrayList<>();

                String queryArtClassString = "select * from " + SqlDBHelper.TABLE_NAME_ART_CLASS + " where art_id=" + artModel.getId();
                Cursor cursor1 = mLiteDatabase.rawQuery(queryArtClassString, null);
                if (cursor1 != null) {
                    while (cursor1.moveToNext()) {
                        ArtSpecliesModel artClassModel = new ArtSpecliesModel();
                        artClassModel.setId(cursor1.getInt(cursor1.getColumnIndex("art_clas_id")));
                        artClassModel.setArtSpecilName(cursor1.getString(cursor1.getColumnIndex("art_clas_name")));
                        artClassModel.setContent(cursor1.getString(cursor1.getColumnIndex("art_clas_content")));
                        artClassModel.setImageUrl(cursor1.getString(cursor1.getColumnIndex("art_clas_creat_image")));
                        artClassModels.add(artClassModel);
                    }

                    cursor1.close();
                }
                //分类
                artModel.specilModels = artClassModels;


                List<ArtClassModel> artClassList = new ArrayList<>();

                String artClassSQL = "select * from " + SqlDBHelper.TABLE_NAME_ART_SPE + " where art_id=" + artModel.getId();

                Cursor cursor2 = mLiteDatabase.rawQuery(artClassSQL, null);
                if (cursor2 != null) {
                    while (cursor2.moveToNext()) {

                        ArtClassModel artClassModel = new ArtClassModel();
                        artClassModel.id = cursor2.getInt(cursor2.getColumnIndex("art_spe_id"));
                        artClassModel.artClassName = cursor2.getString(cursor2.getColumnIndex("art_spe_name"));
                        artClassList.add(artClassModel);
                    }
                    cursor2.close();
                }

                artModel.articleClassModels = artClassList;


                //点赞
                List<String> artPariseList = new ArrayList<>();

                String artPariseSql = "select * from " + SqlDBHelper.TABLE_NAME_RE_ART_PARISE + " where art_id=" + artModel.getId();

                Cursor cursor3 = mLiteDatabase.rawQuery(artPariseSql, null);
                if (cursor3 != null) {
                    while (cursor3.moveToNext()) {
                        artPariseList.add(String.valueOf(cursor3.getInt(cursor3.getColumnIndex("user_id"))));
                    }
                }

                artModel.articlPariseModels = artPariseList;



                //收藏
                List<String> artCollectList = new ArrayList<>();

                String artCollectSql = "select * from " + SqlDBHelper.TABLE_NAME_RE_ART_Collect + " where art_id=" + artModel.getId();

                Cursor artCollectCursor= mLiteDatabase.rawQuery(artCollectSql, null);
                if (artCollectCursor != null) {
                    while (artCollectCursor.moveToNext()) {
                        artCollectList.add(String.valueOf(artCollectCursor.getInt(artCollectCursor.getColumnIndex("user_id"))));
                    }
                }

                artModel.articlCollectModels = artPariseList;



                list.add(artModel);
            }
        }

        lock.release();

        if (latch.getCount() == 1) {
            mLiteDatabase.close();
            mLiteDatabase = null;
        }

        latch.countDown();

        return list;
    }

}
